﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Serialization;

namespace TurboTemplate
{
    public class SchemaHelper
    {
        public static string DataLinksConnection(string toEdit)
        {
            // http://www.codeproject.com/Articles/6080/Using-DataLinks-to-get-or-edit-a-connection-string
            MSDASC.DataLinks dataLinks = new MSDASC.DataLinksClass();
            ADODB._Connection connection;
            if (toEdit == String.Empty)
            {
                try
                {
                    connection = (ADODB._Connection)dataLinks.PromptNew();
                    return connection.ConnectionString.ToString();
                }
                catch
                {
                    return string.Empty;
                }
            }
            else
            {
                // edit connection string
                connection = new ADODB.Connection();
                connection.ConnectionString = toEdit;
                //set local COM compatible data type
                object oConnection = connection;
                try
                {
                    //prompt user to edit the given connect string
                    if ((bool)dataLinks.PromptEdit(ref oConnection))
                    {
                        return connection.ConnectionString;
                    }
                }
                catch
                {
                    return string.Empty;
                }
            }
            return string.Empty;
        }

        public static IDbConnection PrepareConnectionString(string constr)
        {
            if (string.IsNullOrEmpty(constr))
                return null;

            int providerStart = constr.IndexOf("Provider", StringComparison.InvariantCultureIgnoreCase);
            if (providerStart > -1)
            {
                int semiColonAfterProvider = constr.IndexOf(";", providerStart);
                constr = constr.Remove(providerStart, semiColonAfterProvider - providerStart + 1);
            }

            IDbConnection con = new SqlConnection(constr);
            return con;
        }

        public static SchemaSet GetDbSchema(string connectionString)
        {
            SchemaSet ret = new SchemaSet();
            IDbConnection con = PrepareConnectionString(connectionString);

            if (con == null)
                return ret;

            con.Open();

            IDbCommand cmd = new SqlCommand(@"SELECT SPECIFIC_NAME, PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
                                                FROM INFORMATION_SCHEMA.PARAMETERS
                                                ORDER BY SPECIFIC_NAME, ORDINAL_POSITION", (SqlConnection)con);

            IDataReader r = cmd.ExecuteReader();
            Dictionary<string, SchemaStoredProcedure> dicSproc = new Dictionary<string, SchemaStoredProcedure>();

            while (r.Read())
            {
                SchemaStoredProcedure sproc = null;
                string sprocName = (string)r["SPECIFIC_NAME"];

                if (!dicSproc.TryGetValue(sprocName, out sproc))
                {
                    sproc = new SchemaStoredProcedure(sprocName);
                    dicSproc.Add(sprocName, sproc);
                }

                // Add in the parameter
                string dbParamName = (string)r["PARAMETER_NAME"];
                SqlDbType dbType = ResolveDataType((string)r["DATA_TYPE"]);
                int paramSize = r.IsDBNull(r.GetOrdinal("CHARACTER_MAXIMUM_LENGTH")) ? 0 : (int)r["CHARACTER_MAXIMUM_LENGTH"];
                SqlParameter param = new SqlParameter();
                param.SqlDbType = dbType;
                sproc.Parameters.Add(
                    new SchemaParameter() 
                    { 
                        DataType = param.DbType, 
                        ParameterName = dbParamName, 
                        UseNull = false, 
                        Value = null,
                        MaxLength = paramSize
                    });
            }

            ret.Procedures = dicSproc.Select(x => x.Value).ToList();

            r.Close();
            cmd.Cancel();
            cmd.Dispose();

            // Get the tables, no grouping, just put them into our schemaset
            cmd = new SqlCommand(@"SELECT * FROM INFORMATION_SCHEMA.TABLES 
                                   WHERE TABLE_TYPE = 'BASE TABLE' 
                                   ORDER BY TABLE_NAME", (SqlConnection)con);
            r = cmd.ExecuteReader();
            while (r.Read())
            {
                ret.Tables.Add(new SchemaTable((string)r["TABLE_NAME"]));
            }

            // Clear up
            r.Close();
            cmd.Cancel();
            cmd.Dispose();
            con.Close();
            con.Dispose();

            return ret;
        }

        private static SqlDbType ResolveDataType(string dataType)
        {
            return (SqlDbType)Enum.Parse(typeof(SqlDbType), dataType, true);
            //switch (dataType)
            //{
            //    case "int":
            //        return SqlDbType.Int;
            //    case "nvarchar":
            //        return SqlDbType.NVarChar;
            //    case "datetime":
            //        return SqlDbType.DateTime;
            //    case "bit":
            //        return SqlDbType.Bit;
            //    case "timestamp":
            //        return SqlDbType.Timestamp;
            //    case "xml":
            //        return SqlDbType.Xml;
            //    case "money":
            //        return SqlDbType.Money;
            //    case "ntext":
            //        return SqlDbType.NText;
            //    case "varchar":
            //        return SqlDbType.VarChar;
            //    default:
            //        break;
            //}
        }

        public static DataSet ExecuteProcedure(TransformInstructions p)
        {
            IDbConnection con = PrepareConnectionString(p.SourcedFromConnection);
            con.Open();

            IDbCommand cmd = new SqlCommand(p.ProcedureFromSchema.SPECIFIC_NAME, (SqlConnection)con);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (var param in p.ProcedureFromSchema.Parameters)
            {
                IDataParameter newParam = new SqlParameter(param.ParameterName, null);
                newParam.DbType = param.DataType;
                newParam.Value = (param.UseNull ? (object)DBNull.Value : param.Value);
                cmd.Parameters.Add(newParam);
            }

            IDataReader r = cmd.ExecuteReader();

            var ds = new DataSet();
            var dt = new DataTable();
            ds.Tables.Add(dt);
            FillColumnsFromReader(dt, r);

            while (r.NextResult())
            {
                dt = new DataTable();
                ds.Tables.Add(dt);
                FillColumnsFromReader(dt, r);
            }

            return ds;
        }

        private static void FillColumnsFromReader(DataTable dt, IDataReader r)
        {
            for (int i = 0; i < r.FieldCount; i++)
            {
                string name = r.GetName(i);
                Type type = r.GetFieldType(i);
                dt.Columns.Add(name, type);
            }
        }

    }
}
